﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;

namespace KTDS_IPBS_WEB
{
    public struct InfoOfTraceIP
    {
        public String host_id;
        public String host_name;
        public String host_ip;
        public String loc;
        public String type;
        public String name;
        public String dept;
        public String phone;
    }
    public class C_DBConnect
    {
        SqlConnection conn = new SqlConnection();
        //String conStr = @"server=10.225.22.44;Database=DB_IPBS_TEST;uid=sa;pwd=tpgns628";
        String conStr = @"server=10.225.22.47;Database=IPBS;uid=sa;pwd=new1234@";

        public String SelectGroupCode(String ipstr)
        {
            SqlCommand cmd = new SqlCommand();
            String groupcode = null;

            try
            {
                conn.ConnectionString = conStr;

                //실행 쿼리문
                //String query = "select groupcode from TB_IPINFO where = ipinfo'" + ipstr + "';";
                String query = "select parent_host from host_ip where ip='" + ipstr + "';";

                //쿼리 세팅
                cmd.CommandText = query;
                cmd.Connection = conn;

                conn.Open();

                //쿼리 실행결과 담기 - SqlDataReader 또는 SqlDataAdapter를 사용하면 된다.
                SqlDataReader reader = cmd.ExecuteReader();

                //결과값 출력하기
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        groupcode = reader[0].ToString();
                    }
                }

                conn.Close();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }
            
            return groupcode;
        }

        public InfoOfTraceIP SearchInformationOfTraceIP(String ipstr)
        {
            SqlCommand cmd = new SqlCommand();
            InfoOfTraceIP st_info = new InfoOfTraceIP();

            try
            {
                conn.ConnectionString = conStr;

                //실행 쿼리문
                //String query = "select groupcode from TB_IPINFO where = ipinfo'" + ipstr + "';";
                String query = "select hinfo.host_id, hinfo.name, hinfo.loc, hinfo.host_type, pinfo.name, pinfo.dept, pinfo.phone ";
                query += "from host_info hinfo, person_info pinfo ";
                query += "where hinfo.host_id=(select parent_host from host_ip where ip='" + ipstr + "') and hinfo.person=pinfo.id;";

                //쿼리 세팅
                cmd.CommandText = query;
                cmd.Connection = conn;

                conn.Open();

                //쿼리 실행결과 담기 - SqlDataReader 또는 SqlDataAdapter를 사용하면 된다.
                SqlDataReader reader = cmd.ExecuteReader();

                //결과값 출력하기
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        st_info.host_id = reader[0].ToString();
                        st_info.host_name = reader[1].ToString();
                        st_info.loc = reader[2].ToString();
                        st_info.type = reader[3].ToString();
                        st_info.name = reader[4].ToString();
                        st_info.dept = reader[5].ToString();
                        st_info.phone = reader[6].ToString();
                    }
                }

                conn.Close();
            }
            catch (Exception ex)
            {
            }
            finally
            {
                cmd.Dispose();
                conn.Close();
                conn.Dispose();
            }

            return st_info;
        }

        //public List<string> SearchInformationOfTraceIP1(String ipstr)
        //{
        //    SqlCommand cmd = new SqlCommand();
        //    List<string> lst_info = new List<string>();

        //    try
        //    {
        //        conn.ConnectionString = conStr;

        //        //실행 쿼리문
        //        //String query = "select groupcode from TB_IPINFO where = ipinfo'" + ipstr + "';";
        //        String query = "select hinfo.host_id, hinfo.name, hinfo.loc, hinfo.host_type, pinfo.name, pinfo.dept, pinfo.phone ";
        //        query += "from host_info hinfo, person_info pinfo ";
        //        query += "where hinfo.host_id=(select parent_host from host_ip where ip='" + ipstr + "') and hinfo.person=pinfo.id;";

        //        //쿼리 세팅
        //        cmd.CommandText = query;
        //        cmd.Connection = conn;

        //        conn.Open();

        //        //쿼리 실행결과 담기 - SqlDataReader 또는 SqlDataAdapter를 사용하면 된다.
        //        SqlDataReader reader = cmd.ExecuteReader();

        //        //결과값 출력하기
        //        if (reader != null)
        //        {
        //            while (reader.Read())
        //            {
        //                lst_info.Add(reader[0].ToString());
        //                lst_info.Add(reader[1].ToString());
        //                lst_info.Add(reader[2].ToString());
        //                lst_info.Add(reader[3].ToString());
        //                lst_info.Add(reader[4].ToString());
        //                lst_info.Add(reader[5].ToString());
        //                lst_info.Add(reader[6].ToString());
        //            }
        //        }

        //        conn.Close();
        //    }
        //    catch (Exception ex)
        //    {
        //    }
        //    finally
        //    {
        //        cmd.Dispose();
        //        conn.Close();
        //        conn.Dispose();
        //    }

        //    return lst_info;
        //}
    }
}